package config.table.xls;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson2.JSONObject;

import game.tools.fork.ForkJoinTools;
import game.tools.fork.SubForkTask;
import game.tools.utils.ConsoleTable;
import game.tools.utils.JSONTools;
import game.tools.utils.StringTools;
import game.tools.xls.Xls;
import game.tools.xls.XlsXSSF;
import game.tools.xls.XlsXSSF.NumberType;

public class ConfigTableMysql 
{
	public static final ConfigTableMysql Instance = new ConfigTableMysql();
	
	private Connection connection;
	
	private ConsoleTable consoleTable = new ConsoleTable("文件名称" , "数据表名" , "更新行数");
	
	private int fileCount, importCount;
	
	private int segment = 10000;
	
	public void init() throws Exception
	{
		initConnection();
		long importUseTime = executeImport();
		String notifyResultString = serverToNotify();
		
		if(importCount == fileCount)
			consoleTable.setTitle("数据结果，已完成："+importCount+"/"+ fileCount + "，通知：" + notifyResultString + "，导入用时："+ importUseTime);
		else
			consoleTable.setTitle("数据结果，未完成："+importCount+"/"+ fileCount + "，通知：" + notifyResultString + "，导入用时："+ importUseTime);
		System.out.println(consoleTable);
	}

	private String serverToNotify() 
	{
		String notifyResultString = ServerNotify.Instance.toNotify();
		if(notifyResultString == null)
		{
			notifyResultString =  "通知失败";
			consoleTable.setRow(0, "通知错误原因","返回数据为空" , "");
			return notifyResultString;
		}
		
		if(!JSONTools.isJSONObject(notifyResultString))
		{
			notifyResultString =  "通知失败";
			consoleTable.setRow(0, "通知错误原因","返回数据结果错误" , "");
			return notifyResultString;
		}
		
		JSONObject o = JSONObject.parse(notifyResultString);
		String errorReason = o.getString("errorReason");
		if(errorReason != null)
		{
			notifyResultString =  "通知失败";
			consoleTable.setRow(0,"通知错误原因","" , errorReason);
			return notifyResultString;
		}
		
		int successCount = o.getIntValue("successCount");
		if(successCount > 0)
			notifyResultString =  "通知成功("+successCount+")";
		else
			notifyResultString =  "通知失败("+successCount+")";
		
		return notifyResultString;
	}

	private void initConnection()  throws Exception
	{
		Class.forName("com.mysql.cj.jdbc.Driver");
		connection = DriverManager.getConnection("jdbc:mysql://"+Parameters.Instance.getMysqlUrl()+"/"+Parameters.Instance.getMysqlDatabase(), Parameters.Instance.getMysqlUsername(), Parameters.Instance.getMysqlPassword());
	}
	
	private long executeImport() throws Exception
	{
		long importStartTime = System.currentTimeMillis();
		String fileName = null;
		List<String> sqlList = new ArrayList<>();
		try 
		{
			ArrayList<File> fileList = ConfigTableFile.Instance.getFileList();
			fileCount = fileList.size();
			
			for (File file : fileList) 
			{
				sqlList.clear();
				
				fileName = file.getName();
				String tableName = getTableName(fileName);
				System.out.print("===> start import " + fileName + "，");
				
				Xls xls = new XlsXSSF(file.getAbsolutePath() , NumberType.Long);
				int maxRowCount = xls.getRowCount();
				int maxColumnCount = xls.getColumnCount();
				//计算数据列索引列表，排除空列（没有字段名的列）
				List<Integer> dataColumnIndexList = new ArrayList<>(maxColumnCount);
				for (int i = Parameters.Instance.getXlsDataStartColl(); i <= maxColumnCount; i++) 
				{
					String columnName = xls.getCell(Parameters.Instance.getXlsDataCollNameRow(), i);
					if(!StringTools.empty(columnName))
						dataColumnIndexList.add(i);
				}
				
				ArrayList<String> columnTypeList = new ArrayList<>(maxColumnCount); 		//对应列的类型数组。
				String [] createTableResult = createTableSql(tableName , xls , maxRowCount , dataColumnIndexList , columnTypeList);
				String createTableSqlString = createTableResult[0];
				String columnNameSqlString = createTableResult[1];
				long startTime = System.currentTimeMillis();
				String createTableDataSqlString = createTableDataSql(tableName , xls , maxRowCount , dataColumnIndexList , columnTypeList , columnNameSqlString);
				long endTime = System.currentTimeMillis();
				System.out.print(tableName + "：usetime：" + (endTime - startTime) + "，");
				sqlList.add("DROP TABLE IF EXISTS `"+tableName+"`;");
				sqlList.add(createTableSqlString);
				sqlList.add(createTableDataSqlString);
				
				executeSql(sqlList , fileName , tableName);
				xls.close();
			}
		} 
		catch (Exception e) 
		{
			consoleTable.setTitle("数据结果，未完成："+importCount+"/"+ fileCount + "。");
			String errorSqlString = "";
			for (String string : sqlList) 
				errorSqlString += string;
			
			System.err.println("Exception fileName : " + fileName + "，errorSqlString : \n" + errorSqlString + " \n" + e.getMessage());
			System.err.println(consoleTable);
			throw e;
		}
		long importEndTime = System.currentTimeMillis();
		return importEndTime - importStartTime;
	}
	
	private void executeSql(List<String> sqlList, String fileName , String tableName) throws Exception
	{
		Statement stat = null;
		int rowCount = 0;
		try 
		{
			stat = connection.createStatement();
			for (String string : sqlList) 
				stat.addBatch(string);
			int []result = stat.executeBatch();
			rowCount = result[result.length - 1];
			if(rowCount > 0)
				importCount ++;
			
			consoleTable.addRow(fileName , tableName , rowCount);
		}
		catch (Exception e)	{	throw e;	}
		finally
		{
			if(stat != null)
			{
				stat.clearBatch();
				stat.close();
			}
		}
		System.out.println("success ! , rowCount by : " + rowCount);
	}

	private String createTableDataSql(String tableName, Xls xls, int maxRowCount, List<Integer> dataColumnIndexList , ArrayList<String> columnTypeList , String columnNameSqlString) 
	{
		String sqlString = null;
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("INSERT INTO `").append(tableName).append("` (").append(columnNameSqlString).append(") VALUES \n");
		if(maxRowCount > segment)
		{
			StringBuffer sqlDataStringBuffer = multiThreadCreateTableDataSql(tableName , xls , maxRowCount , dataColumnIndexList , columnTypeList , columnNameSqlString);
			sqlStringBuffer.append(sqlDataStringBuffer);
			sqlString = sqlStringBuffer.toString();
		}
		else
		{
			for (int i = Parameters.Instance.getXlsDataStartRow() ; i <= maxRowCount; i++) 
			{
				for (int j = 0 , length = dataColumnIndexList.size(); j < length; j++) 
				{
					int columnIndex = dataColumnIndexList.get(j);
					String value = xls.getCell(i, columnIndex);
					if(value != null)
						value = value.trim();
					
					String type = columnTypeList.get(j);
					if(columnIndex == Parameters.Instance.getXlsDataStartColl())
					{
						if(StringTools.empty(value))
							break;
						sqlStringBuffer.append("(").append(getTypeValue(type , value)).append(",");
					}
					else
					{
						if(j < length -1)
							sqlStringBuffer.append(getTypeValue(type , value )).append(",");
						else
							sqlStringBuffer.append(getTypeValue(type , value )).append("),\n");
					}
				}
			}
			if(sqlStringBuffer.length() > 0)
				sqlString = sqlStringBuffer.substring(0, sqlStringBuffer.length() - 2) + "; ";
		}
		
		return sqlString;
	}

	private StringBuffer multiThreadCreateTableDataSql(String tableName, Xls xls, int maxRowCount, List<Integer> dataColumnIndexList , ArrayList<String> columnTypeList , String columnNameSqlString) 
	{
		int number = maxRowCount / segment;
		StringBuffer sqlStringBuffer = new StringBuffer();
		ForkJoinTools fjForkJoinTools = new ForkJoinTools(number);
		Object [] results;
		try 
		{
			for (int i = 0; i <= number; i++) 
			{
				int start = i * segment;
				int end = start + segment;

				final int rowStart = i == 0 ? start + Parameters.Instance.getXlsDataStartRow() : start+1;
				final int rowEnd = end > maxRowCount ? maxRowCount : end;

				fjForkJoinTools.addTasks(new SubForkTask<String>() 
				{
					@Override
					protected String compute() 
					{
						String sqlString = null;
						StringBuffer threadSqlStringBuffer = new StringBuffer();
//						System.out.println(Thread.currentThread().getName() + " " + rowStart + " " + rowEnd);
						for (int i = rowStart; i <= rowEnd; i++) 
						{
							for (int j = 0, length = dataColumnIndexList.size(); j < length; j++) 
							{
								int columnIndex = dataColumnIndexList.get(j);
								String value = xls.getCell(i, columnIndex);
								if (value != null)
									value = value.trim();

								String type = columnTypeList.get(j);
								if (columnIndex == Parameters.Instance.getXlsDataStartColl()) 
								{
									if (StringTools.empty(value))
										break;
									threadSqlStringBuffer.append("(").append(getTypeValue(type, value)).append(",");
								} 
								else 
								{
									if (j < length - 1)
										threadSqlStringBuffer.append(getTypeValue(type, value)).append(",");
									else
										threadSqlStringBuffer.append(getTypeValue(type, value)).append("),\n");
								}
							}
						}
						if(rowEnd == maxRowCount)
						{
							if(threadSqlStringBuffer.length() > 0)
								sqlString = threadSqlStringBuffer.substring(0, threadSqlStringBuffer.length() - 2) + "; ";
						}
						else 
						{
							sqlString = threadSqlStringBuffer.toString();
						}
						return sqlString;
					}
				});
			}
			results = fjForkJoinTools.submit();
		} 
		finally 
		{
			fjForkJoinTools.terminated();
		}
		
		for (Object string: results) 
			sqlStringBuffer.append(string);
		return sqlStringBuffer;
	}

	private static String[] createTableSql(String tableName, Xls xls, int rowCount, List<Integer> dataColumnIndexList, ArrayList<String> columnTypeList)
	{
		StringBuffer sqlStringBuffer = new StringBuffer();
		sqlStringBuffer.append("CREATE TABLE `").append(tableName).append("` ( \n");
		StringBuffer columnNameBuffer= new StringBuffer();
		boolean fristColumn = false;
		for (int columnIndex : dataColumnIndexList) 
		{
			String columnName = xls.getCell(Parameters.Instance.getXlsDataCollNameRow(), columnIndex);
			if(StringTools.empty(columnName))
				continue;
			fristColumn = columnIndex == Parameters.Instance.getXlsDataStartColl();
			if(!fristColumn)
				columnName = StringTools.toSnakeString(columnName);
			
			String columnDesc = xls.getCell(Parameters.Instance.getXlsDataCollDescRow(), columnIndex);
			String fileDataType = xls.getCell(Parameters.Instance.getXlsDataCollTypeRow(), columnIndex);
			String mysqlDataType = getMysqlDataType(xls ,fileDataType);
			columnTypeList.add(mysqlDataType);
		
			//如果是ID这一列,检测是否为主键，如果不过主键，则添加主键
			if(fristColumn)
			{
				String upperColumnType = mysqlDataType.toUpperCase();
				if(upperColumnType.indexOf("PRIMARY KEY") < 0)		//如果ID这列没人主键，则默认去设置ID为主键		
					mysqlDataType += " PRIMARY KEY";
			}
			
			sqlStringBuffer.append("`").append(columnName).append("` ").append(mysqlDataType).append(" COMMENT '").append(columnDesc).append("', \n");
			columnNameBuffer.append("`").append(columnName).append("`").append(",");
		}
		
		columnNameBuffer.deleteCharAt(columnNameBuffer.length() - 1);
		sqlStringBuffer.deleteCharAt(sqlStringBuffer.length() - 3);
		
		sqlStringBuffer.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8; \n\n");
		String createTableSqlString = sqlStringBuffer.toString();
		return new String[] { createTableSqlString , columnNameBuffer.toString()};
	}
	
	
	private static String getMysqlDataType(Xls xls, String fileType) 
	{
		switch (fileType)
		{
			case "uint32", "int32", "int" -> {	return "int";	}
			case "uint64", "int64" -> {	return "bigint";	}
			case "float" -> {	return "float";	}
			case "string" -> {	return "text";	}
			default ->throw new IllegalArgumentException("Unexpected value: " + fileType);
		}
	}
	
	private static String getTypeValue(String type , String value)
	{
		if(StringTools.empty(value))
			return	"Null";
		
		if(type.indexOf("int") >= 0)
			return value;
		else if(type.indexOf("varchar") >= 0)
			return "'"+value+"'";
		else if(type.indexOf("text") >= 0)
			return "'"+value+"'";
		return	"Null";
	}

	private String getTableName(String fileName)
	{
		fileName = fileName.split("\\.")[0];
		String tableName = null;
		if(Parameters.Instance.getXlsIgnoreSuffix() == null)
			tableName = StringTools.toSnakeString(fileName);
		else
			tableName = StringTools.toSnakeString(fileName.replace(Parameters.Instance.getXlsIgnoreSuffix(), ""));
		return tableName;
	}
}
